package com.kkm.dao.Imp;

import com.kkm.dao.SingerDao;
import com.kkm.pojo.Singer;
import com.kkm.pojo.Song;
import com.kkm.util.JdbcUtils_Druid;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class SingerDaoImp implements SingerDao {
    private JdbcTemplate template=new JdbcTemplate(JdbcUtils_Druid.getDataSource());
    @Override
    public List<Singer> showSinger(int index, int rows) {
        String sql="select * from singer limit ?,?";
        return template.query(sql,new BeanPropertyRowMapper<Singer>(Singer.class),index,rows);
    }

    @Override
    public int checkSingerCount() {
        String sql="select count(*) from singer ";
        return template.queryForObject(sql,int.class);
    }

    @Override
    public int checkSingerNum(Map<String, String[]> condition) {
        String sql="select count(*) from singer where 1=1 ";
        //解析map  1.对sql拼接
        StringBuffer sb=new StringBuffer(sql);
        //2.获取键值，name address，email
        Set<String> keySet=condition.keySet();
        //3.定义参数集合
        List<Object> params=new ArrayList<Object>();
        //遍历看那个不为空拼接到sql语句中
        //key：name，address，email
        for(String key:keySet){
            //排除分页条件参数
            if ("currentPage".equals(key)||"rows".equals(key)||"method".equals(key)){
                continue;
            }
            String value=condition.get(key)[0];//获取值
            if(value!=null||"".equals(value)){
                //有值
                if(key.equals("sexStr")){
                    if(value.equals("男")){
                        sb.append(" and sex= ? ");
                        params.add("1");
                    }else if(value.equals("女")){
                        sb.append(" and sex= ? ");
                        params.add("0");
                    }
                }else {
                    sb.append(" and "+key+" like ? ");
                    params.add("%%"+value+"%");
                }
            }
        }
        System.out.println(sb.toString()+"1");
        return template.queryForObject(sb.toString(),int.class,params.toArray());

    }

    @Override
    public List<Singer> checkSinger(Map<String, String[]> condition, int index, int rows) {
        String sql="select * from singer where 1=1 ";
        //解析map  1.对sql拼接
        StringBuffer sb=new StringBuffer(sql);
        //2.获取键值，name address，email
        Set<String> keySet=condition.keySet();
        //3.定义参数集合
        List<Object> params=new ArrayList<Object>();
        //遍历看那个不为空拼接到sql语句中
        //key：name，address，email
        for(String key:keySet){
            //排除分页条件参数
            if ("currentPage".equals(key)||"rows".equals(key)||"method".equals(key)){
                continue;
            }
            String value=condition.get(key)[0];//获取值
            if(value!=null||"".equals(value)){
                //有值
                if(key.equals("sexStr")){
                    if(value.equals("男")){
                        sb.append(" and sex= ? ");
                        params.add("1");
                    }else if(value.equals("女")){
                        sb.append(" and sex= ? ");
                        params.add("0");
                    }
                }else {
                    sb.append(" and "+key+" like ? ");
                    params.add("%%"+value+"%");
                }
            }
        }
        sb.append(" limit ?,? ");
        params.add(index);
        params.add(rows);
        sql=sb.toString();
        System.out.println(sql+"2");
        return template.query(sql,new BeanPropertyRowMapper<Singer>(Singer.class),params.toArray());
    }

    @Override
    public void deleteSinger(int id) {
        String sql="delete from singer where id=?";
        template.update(sql,id);
    }

    @Override
    public Singer singerById(int sid) {
        String sql="select * from singer where id=?";
       return template.queryForObject(sql,new BeanPropertyRowMapper<Singer>(Singer.class),sid);
    }

    @Override
    public int checkMusicCountById(int sid) {
        String sql="select count(*) from song where singer_id=?";
        return template.queryForObject(sql,int.class,sid);
    }

    @Override
    public List<Song> checkMusicById(int sid, int index, int rows) {
        String sql="select * from song where singer_id=? limit ?,?";
        return template.query(sql,new BeanPropertyRowMapper<Song>(Song.class),sid,index,rows);
    }

    @Override
    public void deleteMusic(int sid) {
        String sql="delete from song where id=?";
        template.update(sql,sid);
    }
}
